package com.alibaba.algo;


import org.apache.commons.lang3.StringUtils;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * The Class DBTools.
 *
 * @author dumengchao
 */
public class DBTools {

    /**
     * The conn.
     */
    private static Connection conn = null;

    /**
     * The stmt.
     */
    private static Statement stmt = null;

    /**
     * The target sql.
     */
    public static String targetSql = "";

    /**
     * Read table column.
     *
     * @param tableName the table name
     * @return the list
     */
    public static List<TableColumn> readTableColumn(String tableName) {

        List<TableColumn> columnList = new ArrayList<TableColumn>();
        try {
            initSql(tableName, GCConfig.getTableSchema());
            getConnection();
            stmt = conn.createStatement();
            TableColumn column = null;
            ResultSet rs = stmt.executeQuery(targetSql);
            while (rs.next()) {
                column = new TableColumn();
                column.setFieldName(Tools.formatField(rs.getString(1)));
                column.setFieldDbName(rs.getString(1));
                column.setDbNameType(Tools.formatDBType(rs.getString(2).toUpperCase()));
                column.setPrecision(Tools.getNullString(rs.getString(4)));
                column.setScale(Tools.getNullString(rs.getString(5)));
                column.setCharmaxLength(Tools.getNullString(rs.getString(6)));
                column.setNullable(Tools.getNullAble(rs.getString(7)));
                column.setFieldType(
                        Tools.formatDataType(rs.getString(2), column.getPrecision(), column.getScale()));
                column.setMyBatisType(Tools.formatDBType(rs.getString(2).toUpperCase()));
                column.setInsertUpdateMyBatisField(
                        "#{" + column.getFieldName() + ",jdbcType=" + column.getMyBatisType().toUpperCase() + "}");
                column.setFiledComment(StringUtils.isBlank(rs.getString(3)) ? column.getFieldName() : rs.getString(3));
                column.setTableDescrition(rs.getString(8));
                columnList.add(column);
            }

        } catch (Exception e) {
            System.out.println(e.getMessage());
        } finally {
            close();
        }
        return columnList;
    }

    /**
     * Gets the db name desc by comma.
     *
     * @param tableName the table name
     * @return the db name desc by comma
     * @throws SQLException           the SQL exception
     * @throws ClassNotFoundException the class not found exception
     */
    public static Map<String, String> getDbNameDescByComma(String tableName)
            throws SQLException, ClassNotFoundException {
        Map<String, String> dbnameMap = new HashMap<String, String>();

        initSql(tableName, GCConfig.getTableSchema());
        getConnection();
        stmt = conn.createStatement();

        ResultSet rs = stmt.executeQuery(targetSql);
        StringBuffer sb = new StringBuffer();
        StringBuffer sb_no_table_id = new StringBuffer();
        while (rs.next()) {
            sb.append(rs.getString(1) + ",");
            if (!rs.getString(1).equals(GCConfig.TABLE_DB_ID)) {
                sb_no_table_id.append(rs.getString(1) + ",");
            }
        }
        if ("".equals(sb_no_table_id.toString())) {
            dbnameMap.put("dbnameNoTableId", "");
        } else {
            dbnameMap.put("dbnameNoTableId", sb_no_table_id.toString().substring(0, sb_no_table_id.length() - 1));
            dbnameMap.put("dbnameWithTableId", sb.toString().substring(0, sb.length() - 1));
        }

        return dbnameMap;
    }

    /**
     * Gets the connection.
     *
     * @return the connection
     * @throws SQLException           the SQL exception
     * @throws ClassNotFoundException the class not found exception
     */
    private static void getConnection() throws SQLException, ClassNotFoundException {
        Class.forName(GCConfig.DIVER_NAME);
        conn = DriverManager.getConnection(GCConfig.URL, GCConfig.USERNAME, GCConfig.PASSWORD);
    }

    /**
     * Close.
     */
    private static void close() {
        try {
            if (stmt != null) {
                stmt.close();
                stmt = null;
                System.gc();
            }
            if (conn != null) {
                conn.close();
                conn = null;
                System.gc();
            }
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }
    }

    /**
     * Inits the sql.
     *
     * @param tableName the table name
     */
    public static void initSql(String tableName, String tableSchema) {
        targetSql = String.format(
                "select col.column_name,col.data_type,col.column_comment,col.numeric_precision,col.numeric_scale,col.character_maximum_length,col.is_nullable nullable, tt.table_comment from information_schema.TABLES tt left join information_schema.columns  col on tt.table_name = col.TABLE_NAME where tt.table_name = '%s' and tt.TABLE_SCHEMA='%s' and col.table_schema='%s'",
                tableName, tableSchema, tableSchema);
    }

    /**
     * Transfer table name.
     *
     * @param tableName the table name
     * @return the string
     */
    public static String transferTableName(String tableName) {
        if (StringUtils.isNotBlank(tableName) && tableName.contains("000")) {
            return tableName.substring(0, tableName.indexOf("000") - 1);
        } else {
            return tableName;
        }
    }

    /**
     * The Class TableColumn.
     *
     * @author dumengchao
     */
    public static class TableColumn {

        /**
         * The table descrition.
         */
        private String tableDescrition;

        /**
         * The field db name.
         */
        private String fieldDbName;

        /**
         * The db name type.
         */
        private String dbNameType;

        /**
         * The field name.
         */
        private String fieldName;

        /**
         * The filed comment.
         */
        private String filedComment;

        /**
         * The field type.
         */
        private String fieldType;

        /**
         * The my batis type.
         */
        private String myBatisType;

        /**
         * The insert update my batis field.
         */
        private String insertUpdateMyBatisField;

        /**
         * The class type.
         */
        private String classType;

        /**
         * The option type.
         */
        private String optionType;

        /**
         * The charmax length.
         */
        private String charmaxLength;

        /**
         * The precision.
         */
        private String precision;

        /**
         * The scale.
         */
        private String scale;

        /**
         * The nullable.
         */
        private String nullable;

        /**
         * Instantiates a new table column.
         */
        public TableColumn() {
            filedComment = "";
            fieldType = "";
            classType = "";
            dbNameType = "";
            optionType = "";
            charmaxLength = "";
        }

        /**
         * Gets the table descrition.
         *
         * @return the table descrition
         */
        public String getTableDescrition() {
            return tableDescrition;
        }

        /**
         * Sets the table descrition.
         *
         * @param tableDescrition the new table descrition
         */
        public void setTableDescrition(String tableDescrition) {
            this.tableDescrition = tableDescrition;
        }

        /**
         * Gets the nullable.
         *
         * @return the nullable
         */
        public String getNullable() {
            return nullable;
        }

        /**
         * Sets the nullable.
         *
         * @param nullable the new nullable
         */
        public void setNullable(String nullable) {
            this.nullable = nullable;
        }

        /**
         * Gets the precision.
         *
         * @return the precision
         */
        public String getPrecision() {
            return precision;
        }

        /**
         * Gets the scale.
         *
         * @return the scale
         */
        public String getScale() {
            return scale;
        }

        /**
         * Sets the precision.
         *
         * @param precision the new precision
         */
        public void setPrecision(String precision) {
            this.precision = precision;
        }

        /**
         * Sets the scale.
         *
         * @param scale the new scale
         */
        public void setScale(String scale) {
            this.scale = scale;
        }

        /**
         * Gets the option type.
         *
         * @return the option type
         */
        public String getOptionType() {
            return optionType;
        }

        /**
         * Sets the option type.
         *
         * @param optionType the new option type
         */
        public void setOptionType(String optionType) {
            this.optionType = optionType;
        }

        /**
         * Gets the class type.
         *
         * @return the class type
         */
        public String getClassType() {
            return classType;
        }

        /**
         * Sets the class type.
         *
         * @param classType the new class type
         */
        public void setClassType(String classType) {
            this.classType = classType;
        }

        /**
         * Gets the field type.
         *
         * @return the field type
         */
        public String getFieldType() {
            return fieldType;
        }

        /**
         * Sets the field type.
         *
         * @param fieldType the new field type
         */
        public void setFieldType(String fieldType) {
            this.fieldType = fieldType;
        }

        /**
         * Gets the field name.
         *
         * @return the field name
         */
        public String getFieldName() {
            return fieldName;
        }

        /**
         * Sets the field name.
         *
         * @param fieldName the new field name
         */
        public void setFieldName(String fieldName) {
            this.fieldName = fieldName;
        }

        /**
         * Gets the filed comment.
         *
         * @return the filed comment
         */
        public String getFiledComment() {
            return filedComment;
        }

        /**
         * Sets the filed comment.
         *
         * @param filedComment the new filed comment
         */
        public void setFiledComment(String filedComment) {
            this.filedComment = filedComment;
        }

        /**
         * Gets the charmax length.
         *
         * @return the charmax length
         */
        public String getCharmaxLength() {
            if (charmaxLength == null || "0".equals(charmaxLength))
                return "";
            else
                return charmaxLength;
        }

        /**
         * Sets the charmax length.
         *
         * @param charmaxLength the new charmax length
         */
        public void setCharmaxLength(String charmaxLength) {
            this.charmaxLength = charmaxLength;
        }

        /**
         * Gets the field db name.
         *
         * @return the field db name
         */
        public String getFieldDbName() {
            return fieldDbName;
        }

        /**
         * Sets the field db name.
         *
         * @param fieldDbName the new field db name
         */
        public void setFieldDbName(String fieldDbName) {
            this.fieldDbName = fieldDbName;
        }

        /**
         * Gets the db name type.
         *
         * @return the db name type
         */
        public String getDbNameType() {
            return dbNameType;
        }

        /**
         * Sets the db name type.
         *
         * @param dbNameType the new db name type
         */
        public void setDbNameType(String dbNameType) {
            this.dbNameType = dbNameType;
        }

        /**
         * Gets the my batis type.
         *
         * @return the my batis type
         */
        public String getMyBatisType() {
            return myBatisType;
        }

        /**
         * Sets the my batis type.
         *
         * @param myBatisType the new my batis type
         */
        public void setMyBatisType(String myBatisType) {
            this.myBatisType = myBatisType;
        }

        /**
         * Gets the insert update my batis field.
         *
         * @return the insert update my batis field
         */
        public String getInsertUpdateMyBatisField() {
            return insertUpdateMyBatisField;
        }

        /**
         * Sets the insert update my batis field.
         *
         * @param insertUpdateMyBatisField the new insert update my batis field
         */
        public void setInsertUpdateMyBatisField(String insertUpdateMyBatisField) {
            this.insertUpdateMyBatisField = insertUpdateMyBatisField;
        }
    }
}
